﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace RookieLibSharp.Database
{
    public class DbExcel : IDatabase
    {
        /// <summary>
        /// 数据库链接前缀
        /// </summary>
        private const string CONNECT_STR_2007 = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};" + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
        private const string CONNECT_STR_2003 = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source={0};" + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";  
        public string DatabaseFilePath { set; get; }
        public DbExcel(string dbFilePath)
        {
            DatabaseFilePath = dbFilePath;
        }
        public DataTable GetAllData(string tableName)
        {
            DataTable dtRet = new DataTable();
            OleDbConnection connection = null;
            try
            {
                string connectionStr = GetConnectStr();
                string cmdStr = "select * from [" + tableName + "$]";
                connection = new OleDbConnection(connectionStr);
                connection.Open();
                OleDbDataReader reader = new OleDbCommand(cmdStr, connection).ExecuteReader();
                dtRet.Load(reader);
                connection.Close();
            }
            catch
            {
            	if (connection != null)
            	{
                    connection.Close();
                    dtRet = null;
            	}
            }

            return dtRet;
        }
        public DataTable ExecuteReader(string command)
        {
            DataTable dtRet = new DataTable();
            string connectionStr = GetConnectStr();
            using (OleDbConnection connection = new OleDbConnection(connectionStr))
            {
                connection.Open();
                OleDbDataReader reader = new OleDbCommand(command, connection).ExecuteReader();
                dtRet.Load(reader);
                connection.Close();
            }
            return dtRet;
        }
        /// <summary>
        /// 执行一个insert update delete操作
        /// </summary>
        /// <param name="queryString">sql语句</param>
        public int ExecuteNonQuery(string queryString)
        {
            string connectionStr = GetConnectStr();
            using (OleDbConnection connection = new OleDbConnection(connectionStr))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand(queryString, connection);
                return command.ExecuteNonQuery();
            }
        }
        private string GetConnectStr()
        {
            string strEx = Path.GetExtension(DatabaseFilePath);
            if (strEx.ToLower() == ".xls")
            {
                return string.Format(CONNECT_STR_2003, DatabaseFilePath);
            }
            else
            {
                return string.Format(CONNECT_STR_2007, DatabaseFilePath);
            }
        }
        public void CloseDatabase()
        {

        }
    }
}
